Excel Version:  Excel 2010 PowerPivots

Problem:  I'm relatively new at PowerPivot Measures but have some experience.  I'm trying to write a measure using the Calculate function which will generate the report, as noted below, from the PowerPivot Data Table, as noted below.

I want to be able to set the pivot table's page filter for "Year" to 2013 (in this example) and generate a report which will show the following information summarized for each contract:

1. The Unbilled Portion-Beginning of Period (at beginning of year selected) for each contract.
2. The Total New Contract, Added Work and Amount Billed for the year for each contract.
3. And the Unbilled Portion-End of Period for each contract.

Here is a link to an Excel file which has the info in the screen shot along with a few of my feeble attempts to write the correct Calculate formula. 

Any help would be greatly appreciated.  I have Jelen's and Collie's books on PowerPivots and neither helps.  They don't give anything but super simple examples.

Regards,

...bob

The PowerPivot Window is blank.
No database, no PivotTable, just junk formatting data.

You are right.  My apologies.  I have revised the file and added a PowerPivots Data Table, a pivot table which is not giving me the correct value for "Unbilled Portion-Beginning of Period" and the desired output.

https://dl.dropboxusercontent.com/u/49380643/2014-03-22%20MSN%20Question.xlsx

Thanks for your help.

Regards,

...bob

PivotTables are not done on data that has already been aggregated,
in your case by month and year.
Provide daily or discrete event data.

Hi Herbert,

Actually this is not aggregated data.  The lowest level of detail in the related SQL table is monthly.  Each year will only have values for the first three months out of the year (Jan, Feb, Mar).  Each record in the SQL table has a Date field with a datetime data type value which represents the first of the month (i.e. 1/1/13 12:00AM).

Is this something that I will need to use PowerPivots Date Intelligence functions on?  If so I assume I will need to bring in a DatesTable??

Here is a revised file which has the Date field with datetime values and the Month using the actual name of the month.  This file also includes some measures that I tried to use to calculate the 'Beginning Unbilled balance" which either resulted in an error or incorrect results.  Hope this helps.

https://dl.dropboxusercontent.com/u/49380643/2014-03-23B%20MSN%20Question.xlsx

I tried to use this formula to create a Measure to calculate the Beginning-Unbilled balance but it summing all rows in the current context filters and not the FIRSTDATE in that range:

Thanks,

Excel 2010, Tables, PowerPivot
With DatesBetween()
http://www.mediafire.com/view/gwmx7hhr50ytfju/03_23_14.xlsx